package simulaco.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import simulaco.dto.Concurso;
import simulaco.dto.ConcursoNivel;
import simulaco.dto.Situacao;


public class ConcursoDAO extends DataAccessObject {

    public static Concurso findByPK(int concursoCodigo, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO         ");
        sbSql.append("     , A.CD_INSTITUICAO      CD_INSTITUICAO      ");
        sbSql.append("     , I.TX_NOME             TX_NOME_INSTITUICAO ");
        sbSql.append("     , A.CD_ORGAO            CD_ORGAO            ");
        sbSql.append("     , O.TX_NOME             TX_NOME_ORGAO       ");
        sbSql.append("     , A.TX_INDENTIFICACAO   TX_INDENTIFICACAO   ");
        sbSql.append("     , A.ID_NIVEL            ID_NIVEL            ");
        sbSql.append("     , A.CD_CARGO            CD_CARGO            ");
        sbSql.append("     , C.TX_NOME             TX_NOME_CARGO       ");
        sbSql.append("     , A.NR_ANO              NR_ANO              ");
        sbSql.append("     , A.DT_PROVA            DT_PROVA            ");
        sbSql.append("     , A.ID_SITUACAO         ID_SITUACAO         ");
        sbSql.append("     , A.TS_CADASTRO         TS_CADASTRO         ");
        sbSql.append("  FROM CONCURSO        A ");
        sbSql.append("     , INSTITUICAO     I ");
        sbSql.append("     , ORGAO           O ");
        sbSql.append("     , CARGO           C ");
        sbSql.append(" WHERE A.CD_INSTITUICAO = I.CD_INSTITUICAO ");
        sbSql.append("   AND A.CD_ORGAO       = O.CD_ORGAO       ");
        sbSql.append("   AND A.CD_CARGO       = C.CD_CARGO       ");
        sbSql.append("   AND A.CD_CONCURSO = ? ");

        Concurso concurso = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoCodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                concurso = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return concurso;
    }

    public static List<Concurso> findAll(Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO         ");
        sbSql.append("     , A.CD_INSTITUICAO      CD_INSTITUICAO      ");
        sbSql.append("     , I.TX_NOME             TX_NOME_INSTITUICAO ");
        sbSql.append("     , A.CD_ORGAO            CD_ORGAO            ");
        sbSql.append("     , O.TX_NOME             TX_NOME_ORGAO       ");
        sbSql.append("     , A.TX_INDENTIFICACAO   TX_INDENTIFICACAO   ");
        sbSql.append("     , A.ID_NIVEL            ID_NIVEL            ");
        sbSql.append("     , A.CD_CARGO            CD_CARGO            ");
        sbSql.append("     , C.TX_NOME             TX_NOME_CARGO       ");
        sbSql.append("     , A.NR_ANO              NR_ANO              ");
        sbSql.append("     , A.DT_PROVA            DT_PROVA            ");
        sbSql.append("     , A.ID_SITUACAO         ID_SITUACAO         ");
        sbSql.append("     , A.TS_CADASTRO         TS_CADASTRO         ");
        sbSql.append("  FROM CONCURSO    A ");
        sbSql.append("     , INSTITUICAO I ");
        sbSql.append("     , ORGAO       O ");
        sbSql.append("     , CARGO       C ");
        sbSql.append(" WHERE A.CD_INSTITUICAO = I.CD_INSTITUICAO ");
        sbSql.append("   AND A.CD_ORGAO       = O.CD_ORGAO       ");
        sbSql.append("   AND A.CD_CARGO       = C.CD_CARGO       ");
        sbSql.append(" ORDER BY A.DT_PROVA ");
        List<Concurso> concursosList = new ArrayList<Concurso>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Concurso concurso = fromResultSet(rs);
                concursosList.add(concurso);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.findAll (" + sqle.getMessage() + ")");
        }
        return concursosList;
    }

    public static List<Concurso> findByFilter(ConcursoFilter filter, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO         ");
        sbSql.append("     , A.CD_INSTITUICAO      CD_INSTITUICAO      ");
        sbSql.append("     , I.TX_NOME             TX_NOME_INSTITUICAO ");
        sbSql.append("     , A.CD_ORGAO            CD_ORGAO            ");
        sbSql.append("     , O.TX_NOME             TX_NOME_ORGAO       ");
        sbSql.append("     , A.TX_INDENTIFICACAO   TX_INDENTIFICACAO   ");
        sbSql.append("     , A.ID_NIVEL            ID_NIVEL            ");
        sbSql.append("     , A.CD_CARGO            CD_CARGO            ");
        sbSql.append("     , C.TX_NOME             TX_NOME_CARGO       ");
        sbSql.append("     , A.NR_ANO              NR_ANO              ");
        sbSql.append("     , A.DT_PROVA            DT_PROVA            ");
        sbSql.append("     , A.ID_SITUACAO         ID_SITUACAO         ");
        sbSql.append("     , A.TS_CADASTRO         TS_CADASTRO         ");
        sbSql.append("  FROM CONCURSO    A ");
        sbSql.append("     , INSTITUICAO I ");
        sbSql.append("     , ORGAO       O ");
        sbSql.append("     , CARGO       C ");
        sbSql.append(" WHERE A.CD_INSTITUICAO = I.CD_INSTITUICAO ");
        sbSql.append("   AND A.CD_ORGAO       = O.CD_ORGAO       ");
        sbSql.append("   AND A.CD_CARGO       = C.CD_CARGO       ");
        if (filter.hasCodigoInstituicao()) {
            sbSql.append(" AND A.CD_INSTITUICAO = ? ");
        }
        if (filter.hasCodigoOrgao()) {
            sbSql.append(" AND A.CD_ORGAO = ? ");
        }
        if (filter.hasCodigoNivel()) {
            sbSql.append(" AND A.ID_NIVEL = ? ");
        }
        if (filter.hasCodigoCargo()) {
            sbSql.append(" AND A.CD_CARGO = ? ");
        }
        sbSql.append(" ORDER BY A.TX_INDENTIFICACAO ");
        List<Concurso> concursosList = new ArrayList<Concurso>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            if (filter.hasCodigoInstituicao()) {
                setInt(ps, pos++, filter.getCodigoInstituicao());
            }
            if (filter.hasCodigoOrgao()) {
                setInt(ps, pos++, filter.getCodigoOrgao());
            }
            if (filter.hasCodigoNivel()) {
                setInt(ps, pos++, filter.getCodigoNivel());
            }
            if (filter.hasCodigoCargo()) {
                setInt(ps, pos++, filter.getCodigoCargo());
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Concurso concurso = fromResultSet(rs);
                concursosList.add(concurso);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.findByFilter (" + sqle.getMessage() + ")");
        }
        return concursosList;
    }

    public static void insert(Concurso concurso, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO CONCURSO     ");
        sbSql.append("     ( CD_CONCURSO       ");
        sbSql.append("     , CD_INSTITUICAO    ");
        sbSql.append("     , CD_ORGAO          ");
        sbSql.append("     , TX_INDENTIFICACAO ");
        sbSql.append("     , ID_NIVEL          ");
        sbSql.append("     , CD_CARGO          ");
        sbSql.append("     , NR_ANO            ");
        sbSql.append("     , DT_PROVA          ");
        sbSql.append("     , ID_SITUACAO       ");
        sbSql.append("     , TS_CADASTRO       ");
        sbSql.append(") VALUES (?,?,?,?,?,?,?,?,?,?)");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concurso.getCodigo());
            setInt(ps, pos++, concurso.getInstituicao().getCodigo());
            setInt(ps, pos++, concurso.getOrgao().getCodigo());
            setString(ps, pos++, concurso.getIdentificacao());
            setInt(ps, pos++, concurso.getNivel().getCodigo());
            setInt(ps, pos++, concurso.getCargo().getCodigo());
            setInt(ps, pos++, concurso.getAno());
            setDate(ps, pos++, concurso.getDataProva());
            setInt(ps, pos++, concurso.getSituacao().getCodigo());
            setTimestamp(ps, pos++, concurso.getDataCadastro());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(Concurso concurso, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE CONCURSO              ");
        sbSql.append("   SET CD_INSTITUICAO    = ? ");
        sbSql.append("     , CD_ORGAO          = ? ");
        sbSql.append("     , TX_INDENTIFICACAO = ? ");
        sbSql.append("     , ID_NIVEL          = ? ");
        sbSql.append("     , CD_CARGO          = ? ");
        sbSql.append("     , NR_ANO            = ? ");
        sbSql.append("     , DT_PROVA          = ? ");
        sbSql.append("     , ID_SITUACAO       = ? ");
        sbSql.append(" WHERE CD_CONCURSO       = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concurso.getInstituicao().getCodigo());
            setInt(ps, pos++, concurso.getOrgao().getCodigo());
            setString(ps, pos++, concurso.getIdentificacao());
            setInt(ps, pos++, concurso.getNivel().getCodigo());
            setInt(ps, pos++, concurso.getCargo().getCodigo());
            setInt(ps, pos++, concurso.getAno());
            setDate(ps, pos++, concurso.getDataProva());
            setInt(ps, pos++, concurso.getSituacao().getCodigo());
            setInt(ps, pos++, concurso.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(Concurso concurso, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM CONCURSO  ");
        sbSql.append(" WHERE CD_CONCURSO = ?");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concurso.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Connection conn, int iRange, int eRange) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_CONCURSO) AS CD_MAX ");
            sbSql.append("  FROM CONCURSO        ");
            sbSql.append(" WHERE CD_CONCURSO > ? ");
            sbSql.append("   AND CD_CONCURSO < ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, iRange);
            setInt(ps, 2, eRange);
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return (max != 0 ? max : iRange) + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static Concurso fromResultSet(ResultSet rs) throws SQLException {

        // CONCURSO
        Concurso concurso = new Concurso();
        concurso.setCodigo(getInt(rs, "CD_CONCURSO"));
        concurso.getInstituicao().setCodigo(getInt(rs, "CD_INSTITUICAO"));
        concurso.getInstituicao().setNome(getString(rs, "TX_NOME_INSTITUICAO"));
        concurso.getOrgao().setCodigo(getInt(rs, "CD_ORGAO"));
        concurso.getOrgao().setNome(getString(rs, "TX_NOME_ORGAO"));
        concurso.setIdentificacao(getString(rs, "TX_INDENTIFICACAO"));
        concurso.setNivel(ConcursoNivel.getNivelById(getByte(rs, "ID_NIVEL")));
        concurso.getCargo().setCodigo(getInt(rs, "CD_CARGO"));
        concurso.getCargo().setNome(getString(rs, "TX_NOME_ORGAO"));
        concurso.setAno(getInt(rs, "NR_ANO"));
        concurso.setDataProva(getDate(rs, "DT_PROVA"));
        concurso.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO")));
        concurso.setDataCadastro(getDateFromTimestamp(rs, "TS_CADASTRO"));
        //
        return concurso;
    }

}
